package pers.nickerg.conver;

import pers.nickerg.bean.ColumnBean;
import pers.nickerg.bean.TableBean;
import pers.nickerg.util.CommonUtil;
import pers.nickerg.util.JdbcUtil;
import pers.nickerg.util.MysqlUtil;
import pers.nickerg.util.OracleUtil;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

/**
 * 将Oracle数据迁移到MySQL
 *
 * @author PF0P65Z6
 */
public class InsertDataToMySQL {
    public static void main(String[] args) throws Exception {
        long start = System.currentTimeMillis();

        int count = 0;
        List<TableBean> tableList = OracleUtil.getAllTableName();
        for (TableBean tableBean : tableList) {
            insertDataToMysql(tableBean.getTableName());
            count++;
        }

        long end = System.currentTimeMillis();
        System.out.println("oracle表数据迁移到mysql已完成，总共涉及表" + count + "张，用时" + (end - start) / 1000 + "秒");
    }

    public static void insertDataToMysql(String tableName) throws IOException {
        long start = System.currentTimeMillis();
        List<ColumnBean> list = OracleUtil.getColList(tableName);
        Connection conn = null;
        PreparedStatement pstm = null;

        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO ").append(tableName.toUpperCase()).append("(");
        for (int i = 0; i < list.size(); i++) {
            ColumnBean c = list.get(i);
            sb.append(c.getColumnName() + ",");
        }
        sb = sb.deleteCharAt(sb.length() - 1);
        sb.append(") values ( ").append(CommonUtil.formatString("?", list.size())).append(")");

        //获取所有oracle列数据
        Properties properties = JdbcUtil.getProperties("oracle.properties");
        ResultSet rs = OracleUtil.getDataFromTable(tableName, properties);
        try {
            properties = JdbcUtil.getProperties("mysql.properties");
            conn = MysqlUtil.getConnection(properties);
            conn.setAutoCommit(false);
            pstm = conn.prepareStatement(sb.toString());
            int count = 0;
            while (rs.next()) {
                count++;
                for (int i = 1; i <= list.size(); i++) {
                    ColumnBean c = list.get(i - 1);
                    setValue(pstm, rs, i, c);
                }

                pstm.addBatch();
                if (count % 1000 == 0 || rs.isLast()) {
                    pstm.executeBatch();
                    conn.commit();
                    pstm.clearBatch();
                    System.out.println("表" + tableName + "正在更新,已更新" + count + "条!");
                }
            }

            long end = System.currentTimeMillis();
            System.out.println("表" + tableName + "，已更新完成!" + "oracle表数据构迁移到mysql，总共更新" + count + "条，用时" + (end - start) / 1000 + "秒");
        } catch (Exception e) {
            System.out.println("表"+tableName+"更新数据失败"+e.getMessage());
            e.printStackTrace();
        } finally {
            MysqlUtil.releaseDB(pstm);
        }
    }

    private static void setValue(PreparedStatement pstm, ResultSet rs, int i, ColumnBean c) throws Exception {
        String type = c.getColumnType();
        String name = c.getColumnName();
        try {
            if ("NUMBER".equalsIgnoreCase(type)) {
                if (c.getDataScale() > 0) {
                    pstm.setDouble(i, rs.getDouble(name));
                } else {
                    pstm.setLong(i, rs.getLong(name));
                }
            } else if ("DATE".equalsIgnoreCase(type)) {
                pstm.setDate(i, rs.getDate(name));
            } else if ("BLOB".equalsIgnoreCase(type)) {
                pstm.setString(i, CommonUtil.blobToString(rs.getBlob(name)));
            } else if ("CLOB".equalsIgnoreCase(type)) {
                pstm.setString(i, CommonUtil.clobToString(rs.getClob(name)));
            } else {
                pstm.setString(i, rs.getString(name));
            }
        }catch (SQLException ex){
            throw new Exception("列"+name+ex.getMessage());
        }
    }
}